﻿CREATE PROCEDURE [dbo].[GetClassData]
	@class_num int
AS
begin

declare @classes table(indexer int, class_num int);
declare @indexer int;
set @indexer = 1;

insert into @classes values(@indexer , @class_num);
set @indexer += 1;

while (@class_num  > 0)
begin
	select @class_num = class_num from ClassesNames where parent_num = @class_num;
	insert into @classes values (@indexer, @class_num);
	set @indexer += 1;
end

DECLARE classes_cursor CURSOR FOR 
SELECT class_num 
FROM @classes 
ORDER BY indexer desc;

OPEN classes_cursor 

FETCH NEXT FROM classes_cursor 
INTO @class_num;

declare @result table(class_num int, FieldName varchar(255), FieldType int);

WHILE @@FETCH_STATUS = 0
BEGIN
    
	insert into @result
		select @class_num, FieldName, FieldType from ClassesFields where class_num = @class_num;
	
	FETCH NEXT FROM classes_cursor 
	INTO @class_num;
END
CLOSE classes_cursor;
DEALLOCATE classes_cursor;

select * from @result

end

